CREATE OR REPLACE PACKAGE BODY pkg_user_auth
IS
	PROCEDURE tde_user_login(
       p_uname            VARCHAR2
	  ,p_interface        VARCHAR2 
      ,p_pass             VARCHAR2 
	  ,p_ip_addr          VARCHAR2 
	  ,p_port             PLS_INTEGER 
	  ,p_pass_eff_dt      VARCHAR2 
	  ,p_psss_exp_dt      VARCHAR2 
	  ,p_user_id    OUT   user_info.user_id%TYPE  -- if match found , return user_id 
	  ,p_ret_msg    OUT   VARCHAR2     -- can be null 
	  ,p_data_msg   OUT   VARCHAR2     -- can be null 
	  ,p_valid_sec  OUT   PLS_INTEGER 
	)
	IS
	    v_user_id      PLS_INTEGER := 0 ;
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(500);	
		v_err          VARCHAR2(200);	
--		v_suffix_err   varchar2(100);
		
		v_curr_pass_eff_dt date; -- varchar2(21);
		v_curr_pass_exp_dt   date; -- varchar2(21);
		
		
		v_curr_pass     user_info.passwordhash%TYPE;
		v_new_pass     user_info.passwordhash%TYPE;
		
		v_pass_eff_dt    DATE;
		v_pass_exp_dt  DATE; 
		
		v_new_eff_dt DATE ;
		v_new_exp_dt   DATE ;
		
		
		v_login_status user_log_hist.login_status%TYPE := 'F';
	BEGIN
		
		SELECT user_id, passwordhash   ,  pass_eff_dt, pass_exp_dt
		INTO v_user_id, v_curr_pass  , v_curr_pass_eff_dt, v_curr_pass_exp_dt  
		FROM user_info 
		WHERE account      = p_uname
		FOR UPDATE ; -- SKIP LOCKED 

		v_pass_eff_dt := to_date( p_pass_eff_dt , 'yyyy/mm/dd hh24:mi:ss');
		v_pass_exp_dt := to_date( p_psss_exp_dt , 'yyyy/mm/dd hh24:mi:ss');
		p_valid_sec := 1;
		v_new_exp_dt := v_pass_exp_dt;
		
		dbms_output.put_line('IN pkg_user_uath.tde_user_login>>> sysdate:'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||  chr(10)||chr(13) ||
		                     ' current_eff_dt:'||v_curr_pass_eff_dt||', curr_exp_dt:'||v_curr_pass_exp_dt ||            chr(10)||chr(13)
							 );
		
		dbms_output.put_line('Para: account:'||p_uname ||', interface:'||p_interface||',eff_dt:'||p_pass_eff_dt||',exp_dt:'||p_psss_exp_dt);
		
		CASE p_interface 
		
		WHEN  'VV' THEN -- 验证身份校验码
		
			p_ret_msg := '验证身份校验码';
			IF p_uname IN ( '13301231234') 
				AND  v_curr_pass  = p_pass AND v_curr_pass_eff_dt = v_pass_eff_dt  and v_curr_pass_exp_dt = v_pass_exp_dt 
			THEN 
				v_login_status := 'S';
			ELSIF  v_curr_pass  = p_pass AND v_curr_pass_eff_dt = v_pass_eff_dt AND v_curr_pass_exp_dt = v_pass_exp_dt 
			    AND sysdate  < v_curr_pass_exp_dt and v_curr_pass_eff_dt < sysdate   ---------Need to be improve later 
			THEN 
				v_login_status := 'S';
			ELSE 
				v_login_status := 'F';
			END IF;	
		WHEN  'DV' THEN  -- 2.2	 
			p_ret_msg := '终止身份校验码';
			IF  v_curr_pass_eff_dt != v_pass_eff_dt  or v_curr_pass_exp_dt != v_pass_exp_dt or v_curr_pass  != p_pass THEN   ---------Need to be improve later 
				v_login_status := 'F';
			ELSE 
				UPDATE user_info 
				SET pass_exp_dt   = pass_eff_dt   
				WHERE user_id = v_user_id
				AND p_uname NOT IN ('13301231234')
				;
				 
			
				v_login_status := 'S';
			END IF;	
		
		WHEN 'VD'  THEN  --- 获取身份校验码 
			p_ret_msg := '获取身份校验码';
			v_msg := ',"beijing":"","eff_dt":" "';  -- only neccessaory for interface VD , null for interface VV 

			IF p_uname IN ( '13301231234') 
				AND  v_curr_pass  = p_pass AND v_curr_pass_eff_dt = v_pass_eff_dt  and v_curr_pass_exp_dt = v_pass_exp_dt 
			THEN 
				v_login_status := 'S';
					v_new_pass     := v_curr_pass ;
					v_new_eff_dt   := v_curr_pass_eff_dt;
					v_new_exp_dt   := v_curr_pass_exp_dt ; --- to_date('2017/12/17 04:03:02','yyyy/mm/dd hh24:mi:ss');
				v_msg := ',"beijing":"'|| v_new_pass ||'","eff_dt":"'|| to_char(v_new_eff_dt,'yyyy/mm/dd hh24:mi:ss') ||'"';

			ELSIF v_curr_pass  != p_pass 
				OR  v_curr_pass_eff_dt != v_pass_eff_dt 
			--	OR  v_pass_exp_dt != v_curr_pass_exp_dt 
				OR  sysdate > v_pass_exp_dt
				OR  v_pass_exp_dt  > sysdate + 1 
			THEN   ---------Need to be improve later 
				v_login_status := 'F';
			ELSE 
				v_login_status := 'S';
				v_new_pass     :=	rawtohex(  DBMS_CRYPTO.Hash ( UTL_I18N.STRING_TO_RAW (
									to_char(sysdate,'hh24:mi:ss')||p_uname||p_pass , 'AL32UTF8'),  3)   ) ;
				v_new_exp_dt   :=	 v_pass_exp_dt;
				v_new_eff_dt   :=sysdate ;

				UPDATE user_info 
				SET pass_eff_dt = v_new_eff_dt
				  , pass_exp_dt   = v_new_exp_dt -- to_date( v_new_exp_dt,'yyyy/mm/dd hh24:mi:ss')
				  , passwordhash  = v_new_pass 
				WHERE user_id = v_user_id
				 ;
				 
				v_msg := ',"beijing":"'|| v_new_pass ||'","eff_dt":"'|| to_char(v_new_eff_dt,'yyyy/mm/dd hh24:mi:ss') ||'"';

				
			END IF;	
		ELSE 
			NULL;

		END CASE ;


		MERGE INTO user_log_hist l 
		USING (
			SELECT v_user_id  user_id
			      , v_login_status login_status
   				  ,'TDE-'||p_interface session_id 
				  , p_ip_addr||':'||p_port  remote_addr
				  , sysdate login_dt 
			FROM DUAL) s 
		ON ( 	l.user_id = s.user_id AND l.login_dt = s.login_dt )
		WHEN MATCHED THEN UPDATE SET l.login_status = s.login_status 
		                            ,l.session_id   = s.session_id
									,l.remote_addr  = s.remote_addr
		WHEN NOT MATCHED THEN INSERT (
							 user_log_hist_id
							,user_id
							,login_status
							,login_dt 
							,session_id
							,remote_addr)
		VALUES ( user_log_hist_id_seq.NEXTVAL
		        ,s.user_id 
				,s.login_status
				,s.login_dt 
 				,s.session_id
				,s.remote_addr 
			);
		COMMIT;
		
		IF v_login_status = 'S' THEN 
			p_user_id  := v_user_id;
			v_flag     := 0;
			p_valid_sec:= (v_new_exp_dt - sysdate )*24*3600+1;
			p_ret_msg  := p_ret_msg ||'成功';
			p_data_msg := '{"flag":'||v_flag||',"interface":"'||p_interface ||'","message":"'||p_ret_msg||'"'||v_msg ||'}';

		ELSE 
			p_user_id  := -1 ;
			v_flag     := -1 ;
			p_ret_msg  := p_ret_msg ||'失败';
			p_data_msg := '{"flag":'||v_flag||',"interface":"'||p_interface ||'", "message":"'||p_ret_msg||'"'||v_msg||'}';

		END IF;


		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN 
			p_user_id := -1;
			p_ret_msg := p_ret_msg||'用户名或密码错.'||replace( DBMS_UTILITY.format_error_backtrace(), '"', '');
			p_data_msg := '{"flag":'||p_user_id||',"interface":"'||p_interface ||'", "message":"用户名或密码错."'||v_msg   ||'}';
		WHEN OTHERS  THEN 
			p_user_id := -2 ;
			p_ret_msg := p_ret_msg||'未知错误:'||replace( sqlerrm(), '"', '')||'>>'||replace( DBMS_UTILITY.format_error_backtrace(), '"', '');
			p_data_msg := '{"flag":'||p_user_id||', "interface":"'||p_interface ||'","message":"'||p_ret_msg||'"'||v_msg ||'}';
			
	
	END tde_user_login;
	
    PROCEDURE user_login (
       p_session_id       VARCHAR2
      ,p_uname            VARCHAR2
      ,p_pass             VARCHAR2 
	  ,p_ip_addr          VARCHAR2 
	  ,p_port             PLS_INTEGER 
	  ,p_user_id    OUT   user_info.user_id%TYPE  -- if match found , return user_id 
	  ,p_ret_msg    OUT   VARCHAR2     -- can be null 
	  ,p_data_msg   OUT   VARCHAR2     -- can be null 
      )
	IS
	    v_user_id      PLS_INTEGER := 0 ;
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
		v_curr_pass         user_info.passwordhash%TYPE;
		v_login_status user_log_hist.login_status%TYPE;
	BEGIN
		SELECT user_id, passwordhash  
		INTO v_user_id, v_curr_pass   
		FROM user_info 
		WHERE account      = p_uname
		;

		user_login_log( v_user_id, v_msg) ; 
		IF v_curr_pass  = p_pass THEN   --------------------------Need to be improve later 
			v_login_status := 'S';
		ELSE 
			v_login_status := 'F';
		END IF;	
		
		
		INSERT INTO user_log_hist (
							 user_log_hist_id
							,user_id
							,login_status
							,session_id
							,remote_addr)
		VALUES ( user_log_hist_id_seq.NEXTVAL
		        ,v_user_id 
				,v_login_status 
 				,p_session_id  
				,p_ip_addr||':'||p_port 
			);
		COMMIT;
		
		IF v_login_status = 'S' THEN 
			p_user_id  := v_user_id;
			v_flag     := 0;
			p_ret_msg  := '登录成功';
		ELSE 
			p_user_id  := -1 ;
			v_flag     := -1 ;
			p_ret_msg  := '登录失败';
		END IF;

		p_data_msg := '{"flag":'||v_flag||', "message":"'||p_ret_msg||'","data":'||nvl(v_msg,'[]') ||'}';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN 
			p_user_id := -1;
			p_ret_msg := '用户名或密码错,'||replace( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), '"', '');
		p_data_msg := '{"flag":'||p_user_id||', "message":"'||p_ret_msg||'","data":'||nvl(v_msg,'[]') ||'}';
		WHEN OTHERS  THEN 
			p_user_id := -2 ;
			p_ret_msg := '未知错误,'||replace( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), '"', '');
		p_data_msg := '{"flag":'||p_user_id||', "message":"'||p_ret_msg||'","data":'||nvl(v_msg,'[]') ||'}';
			
	END user_login ;

    PROCEDURE  user_login_log (
	   p_user_id IN    user_log_hist.user_id%TYPE  
	  ,p_msg     OUT   VARCHAR2     
      )
	IS
	-- This procedure wil return the latest three login IN RECENT 60 DAYS for specific user 
	-- return in Json format :
	--   ' data:[ 
	--     {date:"2016/10/05 17:35", status:"S", "ip addr": xxx.xx  },
	--     {date:"2016/10/05 14:15", status:"F", "ip addr": xxx.xx  },
	--     {date:"2016/10/04 07:30", status:"S", "ip addr": xxx.xx  }
	--   '
	--
	--
	    v_msg  VARCHAR2(4000);

	BEGIN   
		--dbms_output.put_line(' p_user_id = '||p_user_id);
		
		SELECT  '['||
                listagg(  '{ "date":"'|| to_char(login_dt,'yyyy/mm/dd hh24:mi:ss')||'"'||
                          ',"status":"'||login_status||'"'||
						  ',"ip ddr":"'||remote_addr||'"'||
						  '}' , ',' ) 
					  WITHIN GROUP ( order by login_dt DESC )
                  ||']'
			INTO 	v_msg 
		FROM (
			SELECT -- user_id,
			       login_dt,
				   login_status,
				   remote_addr,
				   rank() OVER ( -- partition by user_id
								ORDER BY login_dt DESC )   rn 
			FROM user_log_hist
			WHERE user_id = p_user_id 
			AND   login_dt >= SYSDATE - 60 
			)
		WHERE rn <= 3 
		;	
		
		--dbms_output.put_line(v_msg);
		
		p_msg := v_msg ;
		
	END user_login_log;
	

	PROCEDURE user_register (
				 p_account           user_info.account%TYPE 
				,p_password          user_info.passwordhash%TYPE 
				,p_username          user_info.username%TYPE 
				,p_country_id        user_info.country_id  %TYPE
				,p_addr_id           user_info.addr_id     %TYPE
				,p_addr_detail       user_info.addr_detail%TYPE 
				,p_user_id     OUT   user_info.user_id%TYPE  
	            ,p_ret_msg     OUT   VARCHAR2     -- can be null 
	            ,p_data_msg    OUT   VARCHAR2     -- can be null 
					)
	
	IS
		v_user_id  user_info.user_id%TYPE;
		v_ret_ct   PLS_INTEGER ;
		v_username user_info.username%TYPE;
		
	BEGIN
		IF p_username  IS NULL OR p_username = 'NULL' THEN 
			v_username := p_account;
		ELSE 
			v_username := p_username;
		END IF;
		
		SELECT COUNT(1) INTO v_ret_ct 
		FROM user_info
		WHERE account = p_account 
		  OR  username = v_username ;
		  
		IF v_ret_ct > 0 THEN 
			-- duplicated username or account 
			p_user_id := -1;
			p_ret_msg     := '该电话号或用户名已经注册!';
			p_data_msg   := '{"flag":-1,"message":"'||p_ret_msg||'"}';

		ELSE
			INSERT INTO user_info ( user_id, account, username, passwordhash, status, addr_id, addr_detail )
			VALUES ( user_id_seq.nextval, p_account, v_username, p_password, 'N' , p_addr_id, p_addr_detail )  --- New user   
			RETURN user_id INTO v_user_id;
			
			IF NVL(p_country_id ,0) > 0 THEN 
				UPDATE user_info SET country_id = p_country_id WHERE user_id = v_user_id ;
			END IF;
			COMMIT;
			
			p_user_id    := v_user_id;
			p_ret_msg    := '注册成功';
			p_data_msg   := '{"flag":0,"message":"'||p_ret_msg||'"}';

		END IF;
			
	EXCEPTION 
		WHEN  OTHERS  THEN 
			p_user_id   := -2 ;
			p_ret_msg   := '注册失败： '||replace( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), '"', '')     ; -- SQLERRM ;
		    p_data_msg  := '{"flag":0,"message":"'||p_ret_msg||'"}';

	END user_register;
	
	
   

	PROCEDURE chg_password (
				 p_flag      OUT    PLS_INTEGER
	            ,p_ret_msg   OUT    VARCHAR2     -- can be null 
	            ,p_data_msg  OUT    VARCHAR2     -- can be null 
				,p_user_id      IN  PLS_INTEGER
				,p_session_id   IN  VARCHAR2
				,p_old_pass     IN  VARCHAR2
				,p_new_pass     IN  VARCHAR2
					)
	
	IS
		v_user_id  user_info.user_id%TYPE;
		v_ret_ct   PLS_INTEGER ;
		v_username user_info.username%TYPE;
		
	BEGIN
	
		UPDATE user_info SET 
			passwordhash = p_new_pass
		WHERE user_id = p_user_id
		AND passwordhash = p_old_pass
		;
		
		IF SQL%ROWCOUNT = 0 THEN 
			p_flag := -1 ;
			p_ret_msg:= '用户不存在，或密码不匹配';

--		ELSIF 
		
		
		ELSE 
			p_flag := 0;
			p_ret_msg:= '密码已修改';

		END IF;
		
		p_data_msg := '{"flag":'||p_flag||', "message":"'||p_ret_msg||'"}';
		
		
	END chg_password;



	
	
	
	
END pkg_user_auth;
/
